<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL 一套猛如虎操作 | 仲灏小栈</title>
    <meta name="generator" content="VuePress 1.8.0">
    <link rel="icon" href="/img/favicon.ico">
    <meta name="description" content="专注web全栈学习与总结。JavaScript,js,ES6,TypeScript,vue,python,css3,html5,Node,git,github等技术文章。">
    <meta name="keywords" content="前端博客,个人技术博客,前端,前端开发,前端框架,web前端,前端面试题,技术文档,学习,面试,JavaScript,js,ES6,TypeScript,vue,python,css3,html5,Node,git,github,markdown">
    <meta name="baidu-site-verification" content="code-5CGlqw1B4p">
    <meta name="google-site-verification" content="PbO8RiLRwDnNwqNFNGnaxZnEFB3CqcGMCGoloYfQXV0">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/assets/css/0.styles.15972595.css" as="style"><link rel="preload" href="/assets/js/app.6e09c0e4.js" as="script"><link rel="preload" href="/assets/js/2.26fadcea.js" as="script"><link rel="preload" href="/assets/js/76.6033c6da.js" as="script"><link rel="prefetch" href="/assets/js/10.29078de5.js"><link rel="prefetch" href="/assets/js/100.1775343f.js"><link rel="prefetch" href="/assets/js/101.03c2a233.js"><link rel="prefetch" href="/assets/js/102.2d566277.js"><link rel="prefetch" href="/assets/js/103.28f0aae6.js"><link rel="prefetch" href="/assets/js/104.98d074e6.js"><link rel="prefetch" href="/assets/js/105.a113c54b.js"><link rel="prefetch" href="/assets/js/106.fe975924.js"><link rel="prefetch" href="/assets/js/107.06b48459.js"><link rel="prefetch" href="/assets/js/108.650cfa65.js"><link rel="prefetch" href="/assets/js/109.8dea05a5.js"><link rel="prefetch" href="/assets/js/11.0ffc15c2.js"><link rel="prefetch" href="/assets/js/110.f426e949.js"><link rel="prefetch" href="/assets/js/111.cb0ff462.js"><link rel="prefetch" href="/assets/js/112.2234a7ce.js"><link rel="prefetch" href="/assets/js/113.d42a4afb.js"><link rel="prefetch" href="/assets/js/114.afa6c93d.js"><link rel="prefetch" href="/assets/js/115.e74e58c4.js"><link rel="prefetch" href="/assets/js/116.b52912dc.js"><link rel="prefetch" href="/assets/js/117.f5e176df.js"><link rel="prefetch" href="/assets/js/118.428fdb24.js"><link rel="prefetch" href="/assets/js/119.c7b3c702.js"><link rel="prefetch" href="/assets/js/12.5358dc27.js"><link rel="prefetch" href="/assets/js/120.b28cb8e3.js"><link rel="prefetch" href="/assets/js/121.517216d1.js"><link rel="prefetch" href="/assets/js/122.224b318f.js"><link rel="prefetch" href="/assets/js/123.04e6a56c.js"><link rel="prefetch" href="/assets/js/13.1eb6db46.js"><link rel="prefetch" href="/assets/js/14.2d919975.js"><link rel="prefetch" href="/assets/js/15.351e3a4c.js"><link rel="prefetch" href="/assets/js/16.3acd2d41.js"><link rel="prefetch" href="/assets/js/17.60b03995.js"><link rel="prefetch" href="/assets/js/18.c6ad362d.js"><link rel="prefetch" href="/assets/js/19.8ab677ff.js"><link rel="prefetch" href="/assets/js/20.99f99fea.js"><link rel="prefetch" href="/assets/js/21.c1d6f733.js"><link rel="prefetch" href="/assets/js/22.6f0b0c91.js"><link rel="prefetch" href="/assets/js/23.13368cdd.js"><link rel="prefetch" href="/assets/js/24.c53a9ca6.js"><link rel="prefetch" href="/assets/js/25.cd4407c5.js"><link rel="prefetch" href="/assets/js/26.d9b2ef17.js"><link rel="prefetch" href="/assets/js/27.16352eff.js"><link rel="prefetch" href="/assets/js/28.a62c7d25.js"><link rel="prefetch" href="/assets/js/29.635009f5.js"><link rel="prefetch" href="/assets/js/3.72d46b47.js"><link rel="prefetch" href="/assets/js/30.db885055.js"><link rel="prefetch" href="/assets/js/31.aa8c6cb9.js"><link rel="prefetch" href="/assets/js/32.e63d710c.js"><link rel="prefetch" href="/assets/js/33.4f69bb69.js"><link rel="prefetch" href="/assets/js/34.9d242b1d.js"><link rel="prefetch" href="/assets/js/35.24c7045d.js"><link rel="prefetch" href="/assets/js/36.2988cd72.js"><link rel="prefetch" href="/assets/js/37.05bf63d5.js"><link rel="prefetch" href="/assets/js/38.ecef1ea2.js"><link rel="prefetch" href="/assets/js/39.0fde8ff2.js"><link rel="prefetch" href="/assets/js/4.d95386e0.js"><link rel="prefetch" href="/assets/js/40.99489203.js"><link rel="prefetch" href="/assets/js/41.928918ed.js"><link rel="prefetch" href="/assets/js/42.8ff04c77.js"><link rel="prefetch" href="/assets/js/43.347198e5.js"><link rel="prefetch" href="/assets/js/44.fc4d4540.js"><link rel="prefetch" href="/assets/js/45.5e4eff92.js"><link rel="prefetch" href="/assets/js/46.83a0c228.js"><link rel="prefetch" href="/assets/js/47.37c2f407.js"><link rel="prefetch" href="/assets/js/48.bada4ddc.js"><link rel="prefetch" href="/assets/js/49.562d8b65.js"><link rel="prefetch" href="/assets/js/5.77e36999.js"><link rel="prefetch" href="/assets/js/50.338be56f.js"><link rel="prefetch" href="/assets/js/51.999e3de3.js"><link rel="prefetch" href="/assets/js/52.11309761.js"><link rel="prefetch" href="/assets/js/53.ea6a0c8a.js"><link rel="prefetch" href="/assets/js/54.a49ee06c.js"><link rel="prefetch" href="/assets/js/55.d4d985f2.js"><link rel="prefetch" href="/assets/js/56.258972e6.js"><link rel="prefetch" href="/assets/js/57.e7d3b5af.js"><link rel="prefetch" href="/assets/js/58.50c14a49.js"><link rel="prefetch" href="/assets/js/59.8edb599b.js"><link rel="prefetch" href="/assets/js/6.ba658368.js"><link rel="prefetch" href="/assets/js/60.d1250584.js"><link rel="prefetch" href="/assets/js/61.28187287.js"><link rel="prefetch" href="/assets/js/62.1cb42759.js"><link rel="prefetch" href="/assets/js/63.3fd80d10.js"><link rel="prefetch" href="/assets/js/64.9f43af49.js"><link rel="prefetch" href="/assets/js/65.c4a47594.js"><link rel="prefetch" href="/assets/js/66.cd22b5d7.js"><link rel="prefetch" href="/assets/js/67.5c9918c9.js"><link rel="prefetch" href="/assets/js/68.fb876377.js"><link rel="prefetch" href="/assets/js/69.3414f481.js"><link rel="prefetch" href="/assets/js/7.83e78719.js"><link rel="prefetch" href="/assets/js/70.f003a000.js"><link rel="prefetch" href="/assets/js/71.39859d24.js"><link rel="prefetch" href="/assets/js/72.12912986.js"><link rel="prefetch" href="/assets/js/73.abd327ea.js"><link rel="prefetch" href="/assets/js/74.36cb7d1d.js"><link rel="prefetch" href="/assets/js/75.1cb96b9a.js"><link rel="prefetch" href="/assets/js/77.083624e2.js"><link rel="prefetch" href="/assets/js/78.70d3273c.js"><link rel="prefetch" href="/assets/js/79.14f3aecd.js"><link rel="prefetch" href="/assets/js/8.bcb11f96.js"><link rel="prefetch" href="/assets/js/80.95205bda.js"><link rel="prefetch" href="/assets/js/81.a39e63f0.js"><link rel="prefetch" href="/assets/js/82.6298e8dc.js"><link rel="prefetch" href="/assets/js/83.109c5547.js"><link rel="prefetch" href="/assets/js/84.6a1959a1.js"><link rel="prefetch" href="/assets/js/85.6ff6f61a.js"><link rel="prefetch" href="/assets/js/86.7d2bfbe7.js"><link rel="prefetch" href="/assets/js/87.2032d6ae.js"><link rel="prefetch" href="/assets/js/88.5cda9aa6.js"><link rel="prefetch" href="/assets/js/89.60c9e091.js"><link rel="prefetch" href="/assets/js/9.170a94b1.js"><link rel="prefetch" href="/assets/js/90.0dab9f57.js"><link rel="prefetch" href="/assets/js/91.ecf75d5a.js"><link rel="prefetch" href="/assets/js/92.74397fcb.js"><link rel="prefetch" href="/assets/js/93.f5559b26.js"><link rel="prefetch" href="/assets/js/94.27c65783.js"><link rel="prefetch" href="/assets/js/95.ea55ba1f.js"><link rel="prefetch" href="/assets/js/96.8a68510f.js"><link rel="prefetch" href="/assets/js/97.ef520b02.js"><link rel="prefetch" href="/assets/js/98.d4a41570.js"><link rel="prefetch" href="/assets/js/99.bdd726ad.js">
    <link rel="stylesheet" href="/assets/css/0.styles.15972595.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu have-body-img"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/img/logo.png" alt="仲灏小栈" class="logo"> <span class="site-name can-hide">仲灏小栈</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><a href="/web/" class="nav-link">大前端</a></div><div class="nav-item"><a href="/backend-opt/" class="nav-link">后端&amp;运维</a></div><div class="nav-item"><a href="/other-tech/" class="nav-link">其他</a></div><div class="nav-item"><a href="/tech-live/" class="nav-link">生活</a></div><div class="nav-item"><a href="/about/" class="nav-link">关于我</a></div><div class="nav-item"><a href="/favorite/" class="nav-link">收藏</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/izhaong" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><div class="blogger"><img src="https://cdn.jsdelivr.net/gh/izhaong/izhaong.com-oss/site-data/avatar/avatar-wx.jpg"> <div class="blogger-info"><h3>仲灏</h3> <span>诚意, 正心, 格物, 致知</span></div></div> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><a href="/web/" class="nav-link">大前端</a></div><div class="nav-item"><a href="/backend-opt/" class="nav-link">后端&amp;运维</a></div><div class="nav-item"><a href="/other-tech/" class="nav-link">其他</a></div><div class="nav-item"><a href="/tech-live/" class="nav-link">生活</a></div><div class="nav-item"><a href="/about/" class="nav-link">关于我</a></div><div class="nav-item"><a href="/favorite/" class="nav-link">收藏</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/izhaong" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Java</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Docker</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Nacos</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>数据库</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/71ced0/" class="sidebar-link">redis 笔记</a></li><li><a href="/pages/a43052/" aria-current="page" class="active sidebar-link">MySQL 一套猛如虎操作</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/pages/a43052/#使用docker-compose安装mysql" class="sidebar-link">使用docker-compose安装MySQL</a></li><li class="sidebar-sub-header"><a href="/pages/a43052/#使用docker安装mysql" class="sidebar-link">使用docker安装MySQL</a></li><li class="sidebar-sub-header"><a href="/pages/a43052/#连接mysql" class="sidebar-link">连接MySQL</a></li><li class="sidebar-sub-header"><a href="/pages/a43052/#ddl-操作数据库、表" class="sidebar-link">DDL:操作数据库、表</a></li><li class="sidebar-sub-header"><a href="/pages/a43052/#dml-增删改表中数据" class="sidebar-link">DML：增删改表中数据</a></li><li class="sidebar-sub-header"><a href="/pages/a43052/#多表查询练习" class="sidebar-link">多表查询练习</a></li><li class="sidebar-sub-header"><a href="/pages/a43052/#删除操作" class="sidebar-link">删除操作</a></li><li class="sidebar-sub-header"><a href="/pages/a43052/#常用命令" class="sidebar-link">常用命令</a></li><li class="sidebar-sub-header"><a href="/pages/a43052/#汇总" class="sidebar-link">汇总</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/pages/a43052/#error-1045-28000-access-denied-for-user-root-localhost-using-password-no" class="sidebar-link">ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)</a></li></ul></li></ul></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Nginx</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Linux</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Git</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>其他</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-0c557b5e><div class="articleInfo" data-v-0c557b5e><ul class="breadcrumbs" data-v-0c557b5e><li data-v-0c557b5e><a href="/" title="首页" class="iconfont icon-home router-link-active" data-v-0c557b5e></a></li> <li data-v-0c557b5e><a href="/backend-opt/#后端&amp;运维" data-v-0c557b5e>后端&amp;运维</a></li><li data-v-0c557b5e><a href="/backend-opt/#数据库" data-v-0c557b5e>数据库</a></li></ul> <div class="info" data-v-0c557b5e><div title="作者" class="author iconfont icon-touxiang" data-v-0c557b5e><a href="https://github.com/izhaong" target="_blank" title="作者" class="beLink" data-v-0c557b5e>仲灏</a></div> <div title="创建时间" class="date iconfont icon-riqi" data-v-0c557b5e><a href="javascript:;" data-v-0c557b5e>2021-12-24</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-content"></div></div></div> <h1><img src="">MySQL 一套猛如虎操作<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h2 id="使用docker-compose安装mysql"><a href="#使用docker-compose安装mysql" class="header-anchor">#</a> 使用docker-compose安装MySQL</h2> <p><a href="https://www.yuque.com/attachments/yuque/0/2021/rar/714491/1619169222346-b6efa4fb-f5d3-45e7-a74c-2f8a2f6f73c5.rar" target="_blank" rel="noopener noreferrer">📎docker-compose-mysql.rar<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p> <ul><li><p>上传至服务器</p></li> <li><p>解压</p></li> <li><p>当前目录运行<code>docker-compose up -d</code></p></li> <li><p>就能得到<code>root</code>密码:root的mysql了</p></li></ul> <h2 id="使用docker安装mysql"><a href="#使用docker安装mysql" class="header-anchor">#</a> 使用docker安装MySQL</h2> <p><a href="https://www.cnblogs.com/sablier/p/11605606.html" target="_blank" rel="noopener noreferrer">使用Docker搭建MySQL服务<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p> <p>镜像地址: https://hub.docker.com/_/mysql/</p> <div class="language-dockerfile line-numbers-mode"><pre class="language-dockerfile"><code>docker run -p 3306:3306 --name mysql_study \
-v /usr/local/docker/mysql/conf:/etc/mysql \
-v /usr/local/docker/mysql/logs:/var/log/mysql \
-v /usr/local/docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><ul><li><p><code>-d</code>：容器启动后，在后台运行。</p></li> <li><p><code>--rm</code>：容器终止运行后，自动删除容器文件。</p></li> <li><p><code>-v</code>：主机和容器的目录映射关系，&quot;:&quot;前为主机目录，之后为容器目录</p></li> <li><p><code>--name mysql_study</code>：容器的名字叫做<code>mysql_study</code></p></li> <li><p><code>--e MYSQL_ROOT_PASSWORD=123456</code>：向容器进程传入一个环境变量<code>MYSQL_ROOT_PASSWORD</code>，该变量会被用作 MySQL 的根密码。</p></li></ul> <h2 id="连接mysql"><a href="#连接mysql" class="header-anchor">#</a> 连接MySQL</h2> <ul><li><p>进入容器中</p></li> <li><ul><li><code>docker container exec -it mysql_study /bin/bash</code></li></ul></li> <li><p>登录连接</p></li> <li><ul><li><code>mysql -u root -p123456</code></li></ul></li></ul> <h2 id="ddl-操作数据库、表"><a href="#ddl-操作数据库、表" class="header-anchor">#</a> DDL:操作数据库、表</h2> <ul><li><p>创建数据库</p></li> <li><ul><li><code>create database if not exists demo1 character set utf8;</code></li></ul></li> <li><p>修改数据库的字符集</p></li> <li><ul><li><code>alter database demo1 ``character set utf8mb4;</code></li></ul></li> <li><p>查询所有数据库的名称:</p></li> <li><ul><li><code>show databases;</code></li></ul></li> <li><p>使用数据库</p></li> <li><ul><li>查询当前正在使用的数据库名称</li></ul></li> <li><ul><li><ul><li><code>select database();</code></li></ul></li></ul></li> <li><ul><li>使用数据库</li> <li><code>use demo1;</code></li></ul></li></ul> <hr> <ul><li>创建表</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># 创建部门表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> dept <span class="token punctuation">(</span>
  id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> <span class="token comment">-- 部门id</span>
  dname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 部门名称</span>
  loc <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">-- 部门所在地</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><ul><li><p>查看表</p></li> <li><ul><li>查询某个数据库中所有的表名称</li></ul></li> <li><ul><li><ul><li><code>show tables;</code></li></ul></li></ul></li> <li><ul><li>查询表结构</li></ul></li> <li><ul><li><ul><li><code>desc dept;</code></li></ul></li></ul></li> <li><p>删除</p></li> <li><ul><li><code>drop table dept;</code></li> <li><code>drop table if exists dept;</code></li></ul></li></ul> <h2 id="dml-增删改表中数据"><a href="#dml-增删改表中数据" class="header-anchor">#</a> DML：增删改表中数据</h2> <ul><li><p>添加数据：</p></li> <li><ul><li>语法：</li></ul></li> <li><ul><li><ul><li>insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);</li></ul></li></ul></li> <li><ul><li>注意：</li></ul></li></ul> <ol><li><ol><li><ol><li>列名和值要一一对应。</li> <li>如果表名后，不定义列名，则默认给所有列添加值</li></ol></li></ol></li> <li><ol><li><ol><li><ol><li>into 表名 values(值1,值2,...值n);</li></ol></li></ol></li></ol></li> <li><ol><li><ol><li>除了数字类型，其他类型需要使用引号(单双都可以)引起来</li></ol></li></ol></li></ol> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> dept<span class="token punctuation">(</span>id<span class="token punctuation">,</span>dname<span class="token punctuation">,</span>loc<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>  
<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token string">'教研部'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token string">'学工部'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token string">'销售部'</span><span class="token punctuation">,</span><span class="token string">'广州'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token string">'深圳'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><ul><li><p>删除数据：</p></li> <li><ul><li>语法：</li></ul></li> <li><ul><li><ul><li><code>delete from 表名 [where 条件]</code></li></ul></li></ul></li> <li><ul><li>注意：</li></ul></li></ul> <ol><li><ol><li>如果不加条件，则删除表中所有记录。</li> <li>如果要删除所有记录</li></ol></li> <li><ol><li><ol><li>delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作</li> <li>TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。</li></ol></li></ol></li></ol> <ul><li><p>修改数据：</p></li> <li><ul><li>语法：</li></ul></li> <li><ul><li><ul><li>update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];</li></ul></li></ul></li></ul> <h2 id="多表查询练习"><a href="#多表查询练习" class="header-anchor">#</a> 多表查询练习</h2> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 部门表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> dept <span class="token punctuation">(</span>
  id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">COMMENT</span> <span class="token string">'部门id'</span><span class="token punctuation">,</span> <span class="token comment">-- 部门id</span>
  dname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 部门名称</span>
  loc <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">-- 部门所在地</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 添加4个部门</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> dept<span class="token punctuation">(</span>id<span class="token punctuation">,</span>dname<span class="token punctuation">,</span>loc<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> 
<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token string">'教研部'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token string">'学工部'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token string">'销售部'</span><span class="token punctuation">,</span><span class="token string">'广州'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token string">'深圳'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>



<span class="token comment">-- 职务表，职务名称，职务描述</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> job <span class="token punctuation">(</span>
  id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
  jname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  description <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 添加4个职务</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> job <span class="token punctuation">(</span>id<span class="token punctuation">,</span> jname<span class="token punctuation">,</span> description<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'董事长'</span><span class="token punctuation">,</span> <span class="token string">'管理整个公司，接单'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'经理'</span><span class="token punctuation">,</span> <span class="token string">'管理部门员工'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token string">'销售员'</span><span class="token punctuation">,</span> <span class="token string">'向客人推销产品'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token string">'文员'</span><span class="token punctuation">,</span> <span class="token string">'使用办公软件'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>



<span class="token comment">-- 员工表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp <span class="token punctuation">(</span>
  id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> <span class="token comment">-- 员工id</span>
  ename <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 员工姓名</span>
  job_id <span class="token keyword">INT</span><span class="token punctuation">,</span> <span class="token comment">-- 职务id</span>
  mgr <span class="token keyword">INT</span> <span class="token punctuation">,</span> <span class="token comment">-- 上级领导</span>
  joindate <span class="token keyword">DATE</span><span class="token punctuation">,</span> <span class="token comment">-- 入职日期</span>
  salary <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 工资</span>
  bonus <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 奖金</span>
  dept_id <span class="token keyword">INT</span><span class="token punctuation">,</span> <span class="token comment">-- 所在部门编号</span>
  <span class="token keyword">CONSTRAINT</span> emp_jobid_ref_job_id_fk <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>job_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> job <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">CONSTRAINT</span> emp_deptid_ref_dept_id_fk <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> dept <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 添加员工</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp<span class="token punctuation">(</span>id<span class="token punctuation">,</span>ename<span class="token punctuation">,</span>job_id<span class="token punctuation">,</span>mgr<span class="token punctuation">,</span>joindate<span class="token punctuation">,</span>salary<span class="token punctuation">,</span>bonus<span class="token punctuation">,</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> 
<span class="token punctuation">(</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token string">'孙悟空'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'2000-12-17'</span><span class="token punctuation">,</span><span class="token string">'8000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1002</span><span class="token punctuation">,</span><span class="token string">'卢俊义'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-02-20'</span><span class="token punctuation">,</span><span class="token string">'16000.00'</span><span class="token punctuation">,</span><span class="token string">'3000.00'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1003</span><span class="token punctuation">,</span><span class="token string">'林冲'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-02-22'</span><span class="token punctuation">,</span><span class="token string">'12500.00'</span><span class="token punctuation">,</span><span class="token string">'5000.00'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'唐僧'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1009</span><span class="token punctuation">,</span><span class="token string">'2001-04-02'</span><span class="token punctuation">,</span><span class="token string">'29750.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1005</span><span class="token punctuation">,</span><span class="token string">'李逵'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-09-28'</span><span class="token punctuation">,</span><span class="token string">'12500.00'</span><span class="token punctuation">,</span><span class="token string">'14000.00'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'宋江'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1009</span><span class="token punctuation">,</span><span class="token string">'2001-05-01'</span><span class="token punctuation">,</span><span class="token string">'28500.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1007</span><span class="token punctuation">,</span><span class="token string">'刘备'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1009</span><span class="token punctuation">,</span><span class="token string">'2001-09-01'</span><span class="token punctuation">,</span><span class="token string">'24500.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1008</span><span class="token punctuation">,</span><span class="token string">'猪八戒'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'2007-04-19'</span><span class="token punctuation">,</span><span class="token string">'30000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1009</span><span class="token punctuation">,</span><span class="token string">'罗贯中'</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token string">'2001-11-17'</span><span class="token punctuation">,</span><span class="token string">'50000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1010</span><span class="token punctuation">,</span><span class="token string">'吴用'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-09-08'</span><span class="token punctuation">,</span><span class="token string">'15000.00'</span><span class="token punctuation">,</span><span class="token string">'0.00'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1011</span><span class="token punctuation">,</span><span class="token string">'沙僧'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'2007-05-23'</span><span class="token punctuation">,</span><span class="token string">'11000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1012</span><span class="token punctuation">,</span><span class="token string">'李逵'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-12-03'</span><span class="token punctuation">,</span><span class="token string">'9500.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1013</span><span class="token punctuation">,</span><span class="token string">'小白龙'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'2001-12-03'</span><span class="token punctuation">,</span><span class="token string">'30000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1014</span><span class="token punctuation">,</span><span class="token string">'关羽'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1007</span><span class="token punctuation">,</span><span class="token string">'2002-01-23'</span><span class="token punctuation">,</span><span class="token string">'13000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>



<span class="token comment">-- 工资等级表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> salarygrade <span class="token punctuation">(</span>
  grade <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>   <span class="token comment">-- 级别</span>
  losalary <span class="token keyword">INT</span><span class="token punctuation">,</span>  <span class="token comment">-- 最低工资</span>
  hisalary <span class="token keyword">INT</span> <span class="token comment">-- 最高工资</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 添加5个工资等级</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> salarygrade<span class="token punctuation">(</span>grade<span class="token punctuation">,</span>losalary<span class="token punctuation">,</span>hisalary<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> 
<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">7000</span><span class="token punctuation">,</span><span class="token number">12000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">12010</span><span class="token punctuation">,</span><span class="token number">14000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">14010</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">20010</span><span class="token punctuation">,</span><span class="token number">30000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">30010</span><span class="token punctuation">,</span><span class="token number">99990</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 需求：</span>

<span class="token comment">-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述</span>
<span class="token comment">/*
  分析：
    1.员工编号，员工姓名，工资，需要查询emp表  职务名称，职务描述 需要查询job表
    2.查询条件 emp.job_id = job.id

*/</span>
<span class="token keyword">SELECT</span> 
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token comment">-- 员工编号</span>
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>ename<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token comment">-- 员工姓名</span>
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>salary<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token comment">-- 工资</span>
  t2<span class="token punctuation">.</span><span class="token punctuation">`</span>jname<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token comment">-- 职务名称</span>
  t2<span class="token punctuation">.</span><span class="token punctuation">`</span>description<span class="token punctuation">`</span> <span class="token comment">-- 职务描述</span>
<span class="token keyword">FROM</span> 
  emp t1<span class="token punctuation">,</span> job t2
<span class="token keyword">WHERE</span> 
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>job_id<span class="token punctuation">`</span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">;</span>



<span class="token comment">-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置</span>
<span class="token comment">/*
  分析：
    1. 员工编号，员工姓名，工资 emp  职务名称，职务描述 job  部门名称，部门位置 dept
    2. 条件： emp.job_id = job.id and emp.dept_id = dept.id
*/</span>

<span class="token keyword">SELECT</span> 
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token comment">-- 员工编号</span>
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>ename<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token comment">-- 员工姓名</span>
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>salary<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token comment">-- 工资</span>
  t2<span class="token punctuation">.</span><span class="token punctuation">`</span>jname<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token comment">-- 职务名称</span>
  t2<span class="token punctuation">.</span><span class="token punctuation">`</span>description<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token comment">-- 职务描述</span>
  t3<span class="token punctuation">.</span><span class="token punctuation">`</span>dname<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token comment">-- 部门名称</span>
  t3<span class="token punctuation">.</span><span class="token punctuation">`</span>loc<span class="token punctuation">`</span> <span class="token comment">-- 部门位置</span>
<span class="token keyword">FROM</span> 
  emp t1<span class="token punctuation">,</span> job t2<span class="token punctuation">,</span>dept t3
<span class="token keyword">WHERE</span> 
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>job_id<span class="token punctuation">`</span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token operator">AND</span> t1<span class="token punctuation">.</span><span class="token punctuation">`</span>dept_id<span class="token punctuation">`</span> <span class="token operator">=</span> t3<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">;</span>

<span class="token comment">-- 3.查询员工姓名，工资，工资等级</span>
<span class="token comment">/*
  分析：
    1.员工姓名，工资 emp  工资等级 salarygrade
    2.条件 emp.salary &gt;= salarygrade.losalary and emp.salary &lt;= salarygrade.hisalary
      emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/</span>
<span class="token keyword">SELECT</span> 
  t1<span class="token punctuation">.</span>ename <span class="token punctuation">,</span>
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>salary<span class="token punctuation">`</span><span class="token punctuation">,</span>
  t2<span class="token punctuation">.</span><span class="token operator">*</span>
<span class="token keyword">FROM</span> emp t1<span class="token punctuation">,</span> salarygrade t2
<span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span><span class="token punctuation">`</span>salary<span class="token punctuation">`</span> <span class="token operator">BETWEEN</span> t2<span class="token punctuation">.</span><span class="token punctuation">`</span>losalary<span class="token punctuation">`</span> <span class="token operator">AND</span> t2<span class="token punctuation">.</span><span class="token punctuation">`</span>hisalary<span class="token punctuation">`</span><span class="token punctuation">;</span>



<span class="token comment">-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级</span>
<span class="token comment">/*
  分析：
    1. 员工姓名，工资 emp ， 职务名称，职务描述 job 部门名称，部门位置，dept  工资等级 salarygrade
    2. 条件： emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary

*/</span>
<span class="token keyword">SELECT</span> 
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>ename<span class="token punctuation">`</span><span class="token punctuation">,</span>
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>salary<span class="token punctuation">`</span><span class="token punctuation">,</span>
  t2<span class="token punctuation">.</span><span class="token punctuation">`</span>jname<span class="token punctuation">`</span><span class="token punctuation">,</span>
  t2<span class="token punctuation">.</span><span class="token punctuation">`</span>description<span class="token punctuation">`</span><span class="token punctuation">,</span>
  t3<span class="token punctuation">.</span><span class="token punctuation">`</span>dname<span class="token punctuation">`</span><span class="token punctuation">,</span>
  t3<span class="token punctuation">.</span><span class="token punctuation">`</span>loc<span class="token punctuation">`</span><span class="token punctuation">,</span>
  t4<span class="token punctuation">.</span><span class="token punctuation">`</span>grade<span class="token punctuation">`</span>
<span class="token keyword">FROM</span> 
  emp t1<span class="token punctuation">,</span>job t2<span class="token punctuation">,</span>dept t3<span class="token punctuation">,</span>salarygrade t4
<span class="token keyword">WHERE</span> 
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>job_id<span class="token punctuation">`</span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span> 
  <span class="token operator">AND</span> t1<span class="token punctuation">.</span><span class="token punctuation">`</span>dept_id<span class="token punctuation">`</span> <span class="token operator">=</span> t3<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span>
  <span class="token operator">AND</span> t1<span class="token punctuation">.</span><span class="token punctuation">`</span>salary<span class="token punctuation">`</span> <span class="token operator">BETWEEN</span> t4<span class="token punctuation">.</span><span class="token punctuation">`</span>losalary<span class="token punctuation">`</span> <span class="token operator">AND</span> t4<span class="token punctuation">.</span><span class="token punctuation">`</span>hisalary<span class="token punctuation">`</span><span class="token punctuation">;</span>



<span class="token comment">-- 5.查询出部门编号、部门名称、部门位置、部门人数</span>

<span class="token comment">/*
  分析：
    1.部门编号、部门名称、部门位置 dept 表。 部门人数 emp表
    2.使用分组查询。按照emp.dept_id完成分组，查询count(id)
    3.使用子查询将第2步的查询结果和dept表进行关联查询

*/</span>
<span class="token keyword">SELECT</span> 
  t1<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span>t1<span class="token punctuation">.</span><span class="token punctuation">`</span>dname<span class="token punctuation">`</span><span class="token punctuation">,</span>t1<span class="token punctuation">.</span><span class="token punctuation">`</span>loc<span class="token punctuation">`</span> <span class="token punctuation">,</span> t2<span class="token punctuation">.</span>total
<span class="token keyword">FROM</span> 
  dept t1<span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span>
    dept_id<span class="token punctuation">,</span><span class="token function">COUNT</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span> total
  <span class="token keyword">FROM</span> 
    emp
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> dept_id<span class="token punctuation">)</span> t2
<span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token operator">=</span> t2<span class="token punctuation">.</span>dept_id<span class="token punctuation">;</span>


<span class="token comment">-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询</span>

<span class="token comment">/*
  分析：
    1.姓名 emp， 直接上级的姓名 emp
      * emp表的id 和 mgr 是自关联
    2.条件 emp.id = emp.mgr
    3.查询左表的所有数据，和 交集数据
      * 使用左外连接查询

*/</span>
<span class="token comment">/*
select
  t1.ename,
  t1.mgr,
  t2.`id`,
  t2.ename
from emp t1, emp t2
where t1.mgr = t2.`id`;

*/</span>

<span class="token keyword">SELECT</span> 
  t1<span class="token punctuation">.</span>ename<span class="token punctuation">,</span>
  t1<span class="token punctuation">.</span>mgr<span class="token punctuation">,</span>
  t2<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span>
  t2<span class="token punctuation">.</span><span class="token punctuation">`</span>ename<span class="token punctuation">`</span>
<span class="token keyword">FROM</span> emp t1
<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> emp t2
<span class="token keyword">ON</span> t1<span class="token punctuation">.</span><span class="token punctuation">`</span>mgr<span class="token punctuation">`</span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br><span class="line-number">53</span><br><span class="line-number">54</span><br><span class="line-number">55</span><br><span class="line-number">56</span><br><span class="line-number">57</span><br><span class="line-number">58</span><br><span class="line-number">59</span><br><span class="line-number">60</span><br><span class="line-number">61</span><br><span class="line-number">62</span><br><span class="line-number">63</span><br><span class="line-number">64</span><br><span class="line-number">65</span><br><span class="line-number">66</span><br><span class="line-number">67</span><br><span class="line-number">68</span><br><span class="line-number">69</span><br><span class="line-number">70</span><br><span class="line-number">71</span><br><span class="line-number">72</span><br><span class="line-number">73</span><br><span class="line-number">74</span><br><span class="line-number">75</span><br><span class="line-number">76</span><br><span class="line-number">77</span><br><span class="line-number">78</span><br><span class="line-number">79</span><br><span class="line-number">80</span><br><span class="line-number">81</span><br><span class="line-number">82</span><br><span class="line-number">83</span><br><span class="line-number">84</span><br><span class="line-number">85</span><br><span class="line-number">86</span><br><span class="line-number">87</span><br><span class="line-number">88</span><br><span class="line-number">89</span><br><span class="line-number">90</span><br><span class="line-number">91</span><br><span class="line-number">92</span><br><span class="line-number">93</span><br><span class="line-number">94</span><br><span class="line-number">95</span><br><span class="line-number">96</span><br><span class="line-number">97</span><br><span class="line-number">98</span><br><span class="line-number">99</span><br><span class="line-number">100</span><br><span class="line-number">101</span><br><span class="line-number">102</span><br><span class="line-number">103</span><br><span class="line-number">104</span><br><span class="line-number">105</span><br><span class="line-number">106</span><br><span class="line-number">107</span><br><span class="line-number">108</span><br><span class="line-number">109</span><br><span class="line-number">110</span><br><span class="line-number">111</span><br><span class="line-number">112</span><br><span class="line-number">113</span><br><span class="line-number">114</span><br><span class="line-number">115</span><br><span class="line-number">116</span><br><span class="line-number">117</span><br><span class="line-number">118</span><br><span class="line-number">119</span><br><span class="line-number">120</span><br><span class="line-number">121</span><br><span class="line-number">122</span><br><span class="line-number">123</span><br><span class="line-number">124</span><br><span class="line-number">125</span><br><span class="line-number">126</span><br><span class="line-number">127</span><br><span class="line-number">128</span><br><span class="line-number">129</span><br><span class="line-number">130</span><br><span class="line-number">131</span><br><span class="line-number">132</span><br><span class="line-number">133</span><br><span class="line-number">134</span><br><span class="line-number">135</span><br><span class="line-number">136</span><br><span class="line-number">137</span><br><span class="line-number">138</span><br><span class="line-number">139</span><br><span class="line-number">140</span><br><span class="line-number">141</span><br><span class="line-number">142</span><br><span class="line-number">143</span><br><span class="line-number">144</span><br><span class="line-number">145</span><br><span class="line-number">146</span><br><span class="line-number">147</span><br><span class="line-number">148</span><br><span class="line-number">149</span><br><span class="line-number">150</span><br><span class="line-number">151</span><br><span class="line-number">152</span><br><span class="line-number">153</span><br><span class="line-number">154</span><br><span class="line-number">155</span><br><span class="line-number">156</span><br><span class="line-number">157</span><br><span class="line-number">158</span><br><span class="line-number">159</span><br><span class="line-number">160</span><br><span class="line-number">161</span><br><span class="line-number">162</span><br><span class="line-number">163</span><br><span class="line-number">164</span><br><span class="line-number">165</span><br><span class="line-number">166</span><br><span class="line-number">167</span><br><span class="line-number">168</span><br><span class="line-number">169</span><br><span class="line-number">170</span><br><span class="line-number">171</span><br><span class="line-number">172</span><br><span class="line-number">173</span><br><span class="line-number">174</span><br><span class="line-number">175</span><br><span class="line-number">176</span><br><span class="line-number">177</span><br><span class="line-number">178</span><br><span class="line-number">179</span><br><span class="line-number">180</span><br><span class="line-number">181</span><br><span class="line-number">182</span><br><span class="line-number">183</span><br><span class="line-number">184</span><br><span class="line-number">185</span><br><span class="line-number">186</span><br><span class="line-number">187</span><br><span class="line-number">188</span><br><span class="line-number">189</span><br><span class="line-number">190</span><br><span class="line-number">191</span><br><span class="line-number">192</span><br><span class="line-number">193</span><br><span class="line-number">194</span><br><span class="line-number">195</span><br><span class="line-number">196</span><br><span class="line-number">197</span><br><span class="line-number">198</span><br><span class="line-number">199</span><br><span class="line-number">200</span><br><span class="line-number">201</span><br><span class="line-number">202</span><br><span class="line-number">203</span><br><span class="line-number">204</span><br><span class="line-number">205</span><br><span class="line-number">206</span><br><span class="line-number">207</span><br><span class="line-number">208</span><br><span class="line-number">209</span><br><span class="line-number">210</span><br><span class="line-number">211</span><br><span class="line-number">212</span><br><span class="line-number">213</span><br></div></div><h2 id="删除操作"><a href="#删除操作" class="header-anchor">#</a> 删除操作</h2> <h2 id="常用命令"><a href="#常用命令" class="header-anchor">#</a> 常用命令</h2> <ul><li>停止并删除mysql容器</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>docker stop mysql_study &amp;&amp; docker rm mysql_study
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="汇总"><a href="#汇总" class="header-anchor">#</a> 汇总</h2> <h3 id="error-1045-28000-access-denied-for-user-root-localhost-using-password-no"><a href="#error-1045-28000-access-denied-for-user-root-localhost-using-password-no" class="header-anchor">#</a> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)</h3> <p>重新安装</p></div></div> <!----> <div class="page-edit"><!----> <!----> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">2021/12/30, 13:46:48</span></div></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/pages/71ced0/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">redis 笔记</div></a> <a href="/pages/026210/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">https 配置</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/pages/71ced0/" class="prev">redis 笔记</a></span> <span class="next"><a href="/pages/026210/">https 配置</a>→
      </span></p></div></div></div> <div class="article-list"><div class="article-title"><a href="/archives/" class="iconfont icon-bi">最近更新</a></div> <div class="article-wrapper"><dl><dd>01</dd> <dt><a href="/pages/486e07/"><div>
            Git Hooks
            <!----></div></a> <span class="date">02-24</span></dt></dl><dl><dd>02</dd> <dt><a href="/pages/f041f4/"><div>
            标准化编程规范解决方案
            <!----></div></a> <span class="date">02-24</span></dt></dl><dl><dd>03</dd> <dt><a href="/pages/2b68bd/"><div>
            git 提交信息规范
            <!----></div></a> <span class="date">02-24</span></dt></dl> <dl><dd></dd> <dt><a href="/archives/" class="more">更多文章&gt;</a></dt></dl></div></div></main></div> <div class="footer"><div class="icons"><a href="mailto:izhaong@outlook.com" title="发邮件" target="_blank" class="iconfont icon-youjian"></a><a href="https://github.com/izhaong" title="GitHub" target="_blank" class="iconfont icon-github"></a><a href="https://music.163.com/#/playlist?id=755597173" title="听音乐" target="_blank" class="iconfont icon-erji"></a></div> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2021-2022
    <span>izhaong | <a href="https://github.com/izhaong" target="_blank">github</a> | <a href="http://beian.miit.gov.cn/" target="_blank">蜀ICP备2021031194号</a></span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <div class="body-bg" style="background:url() center center / cover no-repeat;opacity:0.5;"></div> <!----> <!----></div><div class="global-ui"><div></div></div></div>
    <script src="/assets/js/app.6e09c0e4.js" defer></script><script src="/assets/js/2.26fadcea.js" defer></script><script src="/assets/js/76.6033c6da.js" defer></script>
  </body>
</html>
